import pyodbc


class SqlServerHelper:
    def __init__(self, connection_string):
        self.connection_string = connection_string
        self.conn = None

    def connect(self):
        try:
            self.conn = pyodbc.connect(self.connection_string)
        except Exception as e:
            raise

    def disconnect(self):
        if self.conn:
            self.conn.close()


    def execute_insert(self, table_name, columns, values):
        query = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({', '.join(['?'] * len(columns))})"
        return self.execute(query, values, fetch=False)

    def execute_select(self, query, params=None, page=1, per_page=10):
        self.connect()
        cursor = self.conn.cursor()

        try:
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)

            columns = [column[0] for column in cursor.description]
            total_results = cursor.fetchall()

            # 计算分页参数
            total_count = len(total_results)
            start_index = (page - 1) * per_page
            end_index = start_index + per_page

            # 分页结果
            results = [dict(zip(columns, row)) for row in total_results[start_index:end_index]]

            return {
                'code': '00',
                'results': results,
                'total_count': total_count,
                'page': page,
                'per_page': per_page
            }

        except Exception as e:
            return {
                'code': '11',
                'msg': str(e)
            }

        finally:
            cursor.close()
            self.disconnect()

    def execute_delete(self, table_name, conditions):
        if not conditions:
            return {
                'code': '11',
                'msg': '条件为空，无法执行删除操作。'
            }

        condition_columns = " AND ".join([f"{column} = ?" for column in conditions.keys()])
        query = f"DELETE FROM {table_name} WHERE {condition_columns}"
        return self.execute(query, tuple(conditions.values()), fetch=False)

    def execute_update(self, table_name, set_values, conditions):
        if not set_values or not conditions:
            return {
                'code': '11',
                'msg': '要修改的值或条件为空，无法执行更新操作。'
            }

        set_columns = ", ".join([f"{column} = ?" for column in set_values.keys()])
        condition_columns = " AND ".join([f"{column} = ?" for column in conditions.keys()])

        query = f"UPDATE {table_name} SET {set_columns} WHERE {condition_columns}"
        try:
            params = list(set_values.values()) + list(conditions.values())
            return self.execute(query, tuple(params), fetch=False)

        except Exception as e:
            self.conn.rollback()
            return {
                'code': '11',
                'msg': str(e)
            }

    def execute_select_with_conditions(self, table_name, conditions=None, page=1, per_page=10):
        self.connect()
        cursor = self.conn.cursor()

        try:
            # 构建用于获取分页数据的查询
            query = f"SELECT * FROM {table_name}"

            if conditions:
                params = []
                where_clause = " WHERE 1 = 1"
                for column, value in conditions.items():
                    if value is not None and value != "" and column not in ('recheckStartTime', 'recheckEndTime'):
                        if isinstance(value, str):
                            where_clause += f" AND {column} LIKE ?"
                            params.append(f"{value}%")
                        else:
                            where_clause += f" AND {column} = ?"
                            params.append(value)

                if 'recheckStartTime' in conditions and 'recheckEndTime' in conditions and conditions[
                    'recheckStartTime'] is not None and conditions['recheckEndTime'] is not None:
                    where_clause += " AND recheckStartTime >= ? AND recheckEndTime <= ?"
                    params.extend([conditions['recheckStartTime'], conditions['recheckEndTime']])

                query += where_clause

            query += f" ORDER BY id OFFSET {((page - 1) * per_page)} ROWS FETCH NEXT {per_page} ROWS ONLY"

            # 执行用于获取分页数据的查询
            cursor.execute(query, params if conditions else None)

            columns = [column[0] for column in cursor.description]
            total_results = cursor.fetchall()

            # 构建用于获取总记录数的查询
            count_query = f"SELECT COUNT(*) FROM {table_name}"

            if conditions:
                count_query += where_clause

            # 执行用于获取总记录数的查询
            cursor.execute(count_query, params if conditions else None)
            total_count = cursor.fetchone()[0]

            results = [dict(zip(columns, row)) for row in total_results]

            return {
                'code': '00',
                'results': results,
                'total_count': total_count,
                'page': page,
                'per_page': per_page
            }

        except Exception as e:
            return {
                'code': '11',
                'msg': str(e)
            }

        finally:
            cursor.close()
            self.disconnect()

    # def execute_select_with_conditions(self, table_name, conditions=None, page=1, per_page=10):
    #     self.connect()
    #     cursor = self.conn.cursor()
    #
    #     try:
    #         # 构建 SQL 查询的基础字符串
    #         query = f"SELECT * FROM {table_name}"
    #
    #         # 根据条件动态添加 WHERE 子句
    #         if conditions:
    #             params = []
    #             where_clause = " WHERE 1 = 1"
    #             for column, value in conditions.items():
    #                 if value is not None and value != "" and column != 'recheckStartTime' and column != 'recheckEndTime':
    #                     if isinstance(value, str):
    #                         # 如果值是字符串类型，使用模糊搜索
    #                         where_clause += f" AND {column} LIKE ?"
    #                         params.append(f"%{value}%")
    #                     else:
    #                         where_clause += f" AND {column} = ?"
    #                         params.append(value)
    #
    #             # 添加时间段查询条件
    #             if 'recheckStartTime' in conditions and 'recheckEndTime' in conditions and conditions['recheckStartTime'] is not None and conditions['recheckEndTime'] is not None :
    #                 where_clause += " AND recheckStartTime >= ? AND recheckEndTime <= ?"
    #                 params.append(conditions['recheckStartTime'])
    #                 params.append(conditions['recheckEndTime'])
    #
    #             # 拼接条件和查询字符串
    #             query += where_clause
    #
    #         # 执行查询
    #         cursor.execute(query, params if conditions else None)
    #
    #         columns = [column[0] for column in cursor.description]
    #         total_results = cursor.fetchall()
    #         # 计算总记录数
    #         total_count = len(total_results)
    #
    #         # 分页参数
    #         start_index = (page - 1) * per_page
    #         end_index = start_index + per_page
    #
    #         # 分页结果
    #         results = [dict(zip(columns, row)) for row in total_results[start_index:end_index]]
    #
    #         return {
    #             'code': '00',
    #             'results': results,
    #             'total_count': total_count,
    #             'page': page,
    #             'per_page': per_page
    #         }
    #
    #     except Exception as e:
    #         return {
    #             'code': '11',
    #             'msg': str(e)
    #         }
    #
    #     finally:
    #         cursor.close()
    #         self.disconnect()

    def execute_select_without_pagination(self, query, params=None):
        self.connect()
        cursor = self.conn.cursor()

        try:
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)

            columns = [column[0] for column in cursor.description]
            results = [dict(zip(columns, row)) for row in cursor.fetchall()]

            return {
                'code': '00',
                'results': results
            }

        except Exception as e:
            return {
                'code': '11',
                'msg': str(e)
            }

        finally:
            cursor.close()
            self.disconnect()
    # 查询数据是否存在
    def does_record_exist(self, table_name, conditions):

            condition_list = [f"{column} = '{value}' and {column}  != ''" for column, value in conditions.items()]
            query = f"SELECT CASE WHEN EXISTS(SELECT TOP 1 1 FROM {table_name} WHERE " + " AND ".join(
                condition_list) + ") THEN '1' ELSE '0' END"
            self.connect()

            try:
                with self.conn.cursor() as cursor:
                    cursor.execute(query)  # 不传递 conditions.values() 作为参数
                    result = cursor.fetchone()
                    return {
                        'code': '00',
                        'results': result[0] == '1'
                    }
            except Exception as e:
                return {
                    'code': '11',
                    'msg': str(e)
                }

    def execute(self, query, params=None, fetch=True):
        self.connect()

        with self.conn, self.conn.cursor() as cursor:
            try:
                if params:
                    cursor.execute(query, params)
                else:
                    cursor.execute(query)

                if fetch:
                    columns = [column[0] for column in cursor.description]
                    results = [dict(zip(columns, row)) for row in cursor.fetchall()]
                    return {
                        'code': '00',
                        'results': results
                    }
                else:
                    self.conn.commit()
                    return {
                        'code': "00" if cursor.rowcount != 0 else "11",
                    }

            except Exception as e:
                return {
                    'code': '11',
                    'msg': str(e)
                }




    # 临时加上编码信息查找功能，Bid 后期优化
    def getCodeDataList(self, table_name, conditions=None, page=1, per_page=10):
      self.connect()
      cursor = self.conn.cursor()

      try:
          # 构建查询条件
          query = f"SELECT * FROM {table_name}"

          params = []
          where_clause = " WHERE 1 = 1"  # 默认条件，避免条件为空时出现语法错误

          if conditions:
              for column, value in conditions.items():
                  if value is not None and value != "":
                      if isinstance(value, str):
                          where_clause += f" AND {column} LIKE ?"
                          params.append(f"{value}%")  # 前缀模糊匹配
                      else:
                          where_clause += f" AND {column} = ?"
                          params.append(value)

          query += where_clause
          # print(query)
          # 分页查询，确保分页语句在查询语句之后
          query += f" ORDER BY BId OFFSET {((page - 1) * per_page)} ROWS FETCH NEXT {per_page} ROWS ONLY"

          # 执行查询
          cursor.execute(query, params)
          columns = [column[0] for column in cursor.description]
          results = cursor.fetchall()

          # 获取总记录数
          count_query = f"SELECT COUNT(*) FROM {table_name} {where_clause}"

          cursor.execute(count_query, params)
          total_count = cursor.fetchone()[0]
          # 将查询结果转换为字典列表
          results_dict = [dict(zip(columns, row)) for row in results]
          return {
              'code': '00',
              'results': results_dict,
              'total_count': total_count,
              'page': page,
              'per_page': per_page
          }

      except Exception as e:
          # 捕获异常并返回错误信息
          return {
              'code': '11',
              'msg': str(e)
          }

      finally:
          # 确保游标关闭
          cursor.close()
          self.disconnect()
